/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package jlibrarian;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import javax.sql.DataSource;
import jlibrarian.entities.Book;
import jlibrarian.entities.Customer;
import jlibrarian.entities.Rent;
import jlibrarian.interfaces.RentManager;

/**
 *
 * @author dandelion8
 */
public class RentManagerImpl implements RentManager
{
    private DataSource pool;
    
    public RentManagerImpl(DataSource pool)
    {
        this.pool = pool;
    }

    public synchronized Rent createRent(Rent rent)
    {
        int rid = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try{
            conn = pool.getConnection();
            ps = conn.prepareStatement("INSERT INTO rents (cid, bid, borrowed, toReturn, returned) VALUES(?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

            ps.setInt(1, rent.getCustomer().getCid());
            ps.setInt(2, rent.getBook().getBid());
            ps.setTimestamp(3, new Timestamp(rent.getBorrowed().getTime().getTime()));
            ps.setTimestamp(4, new Timestamp(rent.getToReturn().getTime().getTime()));
            
            if(rent.getReturned()!=null)
            {
                ps.setTimestamp(5, new Timestamp(rent.getReturned().getTime().getTime()));
            }else{
                 ps.setTimestamp(5,null);
            }
            ps.execute();

            // gets keyes
            ResultSet keys = ps.getGeneratedKeys();
            keys.next();
            rid = keys.getInt(1);
        }catch(SQLException e)
        {
            throw new JLibException(e);        
        }
        finally
        {
            // close
            try{
                if(ps != null) ps.close();
                if(conn != null) conn.close();
            }catch(SQLException e)
            {
                throw new JLibException(e);
            }
        }
        
        // sets keys
        rent.setRid(rid);
        
       return rent;
    }

    public synchronized boolean updateRent(Rent rent)
    {
        if(rent.getRid() == 0)
        {
            throw new IllegalArgumentException("Rent ID can't be null.");
        }
         
        Connection conn = null;
        PreparedStatement ps = null;
        int rows = 0;

        try{
            conn = pool.getConnection();
            ps = conn.prepareStatement("UPDATE rents SET cid = ?, bid = ?, borrowed = ?, returned = ?, toReturn = ? WHERE rid = ?");

            ps.setInt(1, rent.getCustomer().getCid());
            ps.setInt(2, rent.getBook().getBid());
            ps.setTimestamp(3, new Timestamp(rent.getBorrowed().getTime().getTime()));
            if(rent.getReturned() != null){
                ps.setTimestamp(4, new Timestamp(rent.getReturned().getTime().getTime()));
            }else{
                ps.setNull(4, java.sql.Types.TIMESTAMP);
            }
            ps.setTimestamp(5, new Timestamp(rent.getToReturn().getTime().getTime()));
            ps.setInt(6, rent.getRid());
            
            rows = ps.executeUpdate();

        }catch(SQLException e)
        {
            throw new JLibException(e);        
        }
        finally
        {
            // close
            try{
                if(ps != null) ps.close();
                if(conn != null) conn.close();
            }catch(SQLException e)
            {
                throw new JLibException(e);
            }
        }
       
        return (rows != 0);
    }

    public synchronized boolean deleteRent(Rent rent)
    {
        if(rent.getRid() == 0)
        {
            throw new IllegalArgumentException("Rent ID can't be null.");
        }
        
        int rows = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        
        try{
            // creating connection
            conn = this.pool.getConnection();
            ps = conn.prepareStatement("DELETE FROM rents WHERE rid = ?");
            ps.setInt(1, rent.getRid());
            rows = ps.executeUpdate();
        }catch(SQLException e)
        {
            throw new JLibException(e);
        }
        finally
        {
            // close
            try{
                if(ps != null) ps.close();
                if(conn != null) conn.close();
            }catch(SQLException e)
            {
                throw new JLibException(e);
            }
        }
        
        return (rows != 0);
    }
    
    

    public synchronized Collection<Rent> findRentsByBook(Book book) {
        return this.findRentsByBook(book, false);
    }

    public synchronized Collection<Rent> findRentsByBook(Book book, boolean searchInReturned) {
        
        Collection<Rent> rents = new ArrayList<Rent>();

         // updated rows
        Connection conn = null;
        PreparedStatement ps = null;

        
        try{

            conn = pool.getConnection();

            // where to search
            if(searchInReturned)
            {
                ps = conn.prepareStatement("SELECT * FROM rents WHERE bid = ?");
            }else{
                ps = conn.prepareStatement("SELECT * FROM rents WHERE bid = ? AND returned IS NULL");
            }
            ps.setInt(1, book.getBid());
              
            ResultSet result = ps.executeQuery();

            // select the first result
            while(result.next())
            {
                Rent r = new Rent();
                r.setRid(result.getInt("rid"));
                r.setBook(new BookManagerImpl(pool).findBookById(result.getInt("bid")));
                r.setCustomer(new CustomerManagerImpl(pool).findCustomerById(result.getInt("cid")));
                
                Calendar borrowed = Calendar.getInstance();
                borrowed.setTimeInMillis(result.getTimestamp("borrowed").getTime());
                r.setBorrowed(borrowed);
                
                if(result.getTimestamp("returned") != null)
                {
                    Calendar returned = Calendar.getInstance();
                    returned.setTimeInMillis(result.getTimestamp("returned").getTime());
                    r.setReturned(returned);
                }
                
                Calendar toReturn = Calendar.getInstance();
                toReturn.setTimeInMillis(result.getTimestamp("toReturn").getTime());
                r.setToReturn(toReturn);
                
                rents.add(r);
            }

        }catch(SQLException e)
        {
            throw new JLibException(e);
        }
        finally
        {
            // close
            try{
                if(ps != null) ps.close();
                if(conn != null) conn.close();
            }catch(SQLException e)
            {
                throw new JLibException(e);
            }
        }
        return rents;
    }

    public synchronized Collection<Rent> findRentsByCustomer(Customer customer) {
        return this.findRentsByCustomer(customer, false);
    }
    
    public synchronized int findNumberOfCurrentRentsByBook(Book b){
        Connection conn = null;
        PreparedStatement ps = null;
        int count = -1;

        try{

            conn = pool.getConnection();

            // where to search
            ps = conn.prepareStatement("SELECT count(*) FROM rents WHERE returned IS NULL AND bid = ?");
            ps.setInt(1, b.getBid());
         
            ResultSet result = ps.executeQuery();
            
            // select the first result
            while(result.next())
            {
                count = result.getInt(1);
            }

        }catch(SQLException e)
        {
            throw new JLibException(e);
        }
        finally
        {
            // close
            try{
                if(ps != null) ps.close();
                if(conn != null) conn.close();
            }catch(SQLException e)
            {
                throw new JLibException(e);
            }
        }
        return count;
    
    }

    public synchronized Collection<Rent> findRentsByCustomer(Customer customer, boolean searchInReturned) {
                
        Collection<Rent> rents = new ArrayList<Rent>();

        Connection conn = null;
        PreparedStatement ps = null;

        try{

            conn = pool.getConnection();

            // where to search
            ps = conn.prepareStatement("SELECT * FROM rents WHERE cid = ?");
            ps.setInt(1, customer.getCid());
         
            ResultSet result = ps.executeQuery();

            // select the first result
            while(result.next())
            {
                Rent r = new Rent();
                r.setRid(result.getInt("rid"));
                r.setBook(new BookManagerImpl(pool).findBookById(result.getInt("bid")));
                r.setCustomer(new CustomerManagerImpl(pool).findCustomerById(result.getInt("cid")));
                
                Calendar borrowed = Calendar.getInstance();
                borrowed.setTimeInMillis(result.getTimestamp("borrowed").getTime());
                r.setBorrowed(borrowed);
                
                if(result.getTimestamp("returned") != null)
                {
                    Calendar returned = Calendar.getInstance();
                    returned.setTimeInMillis(result.getTimestamp("returned").getTime());
                    r.setReturned(returned);
                }
                
                Calendar toReturn = Calendar.getInstance();
                toReturn.clear();
                toReturn.setTimeInMillis(result.getTimestamp("toReturn").getTime());
                r.setToReturn(toReturn);
                
                rents.add(r);
            }

        }catch(SQLException e)
        {
            throw new JLibException(e);
        }
        finally
        {
            // close
            try{
                if(ps != null) ps.close();
                if(conn != null) conn.close();
            }catch(SQLException e)
            {
                throw new JLibException(e);
            }
        }
        return rents;
    }
    
    
    
    
    public Collection<Rent> getAllRents(){
        return this.getAllRents(false);
    }
    
    public synchronized Collection<Rent> getAllRents(boolean searchInReturned) {
                
        Collection<Rent> rents = new ArrayList<Rent>();

        Connection conn = null;
        PreparedStatement ps = null;

        try{

            conn = pool.getConnection();

            // where to search
            if(searchInReturned)
            {
                ps = conn.prepareStatement("SELECT * FROM rents WHERE returned IS NOT NULL ORDER BY borrowed DESC");
            }else{
                ps = conn.prepareStatement("SELECT * FROM rents WHERE returned IS NULL ORDER BY borrowed ASC");
            }
         
            ResultSet result = ps.executeQuery();

            // select the first result
            while(result.next())
            {
                Rent r = new Rent();
                r.setRid(result.getInt("rid"));
                r.setBook(new BookManagerImpl(pool).findBookById(result.getInt("bid")));
                r.setCustomer(new CustomerManagerImpl(pool).findCustomerById(result.getInt("cid")));
                
                Calendar borrowed = Calendar.getInstance();
                borrowed.setTimeInMillis(result.getTimestamp("borrowed").getTime());
                r.setBorrowed(borrowed);
                
                if(result.getTimestamp("returned") != null)
                {
                    Calendar returned = Calendar.getInstance();
                    returned.setTimeInMillis(result.getTimestamp("returned").getTime());
                    r.setReturned(returned);
                }
                
                Calendar toReturn = Calendar.getInstance();
                toReturn.clear();
                toReturn.setTimeInMillis(result.getTimestamp("toReturn").getTime());
                r.setToReturn(toReturn);
                
                rents.add(r);
            }

        }catch(SQLException e)
        {
            throw new JLibException(e);
        }
        finally
        {
            // close
            try{
                if(ps != null) ps.close();
                if(conn != null) conn.close();
            }catch(SQLException e)
            {
                throw new JLibException(e);
            }
        }
        return rents;
    }
    
    
     public synchronized Rent findRentById(int rid) {
                
        Rent r = null;

        Connection conn = null;
        PreparedStatement ps = null;

        try{

            conn = pool.getConnection();

            // where to search
           
                ps = conn.prepareStatement("SELECT * FROM rents WHERE rid = ?");
            ps.setInt(1, rid);
            ResultSet result = ps.executeQuery();

            // select the first result
            while(result.next())
            {
                r = new Rent();
                r.setRid(result.getInt("rid"));
                r.setBook(new BookManagerImpl(pool).findBookById(result.getInt("bid")));
                r.setCustomer(new CustomerManagerImpl(pool).findCustomerById(result.getInt("cid")));
                
                Calendar borrowed = Calendar.getInstance();
                borrowed.setTimeInMillis(result.getTimestamp("borrowed").getTime());
                r.setBorrowed(borrowed);
                
                if(result.getTimestamp("returned") != null)
                {
                    Calendar returned = Calendar.getInstance();
                    returned.setTimeInMillis(result.getTimestamp("returned").getTime());
                    r.setReturned(returned);
                }
                
                Calendar toReturn = Calendar.getInstance();
                toReturn.clear();
                toReturn.setTimeInMillis(result.getTimestamp("toReturn").getTime());
                r.setToReturn(toReturn);

            }

        }catch(SQLException e)
        {
            throw new JLibException(e);
        }
        finally
        {
            // close
            try{
                if(ps != null) ps.close();
                if(conn != null) conn.close();
            }catch(SQLException e)
            {
                throw new JLibException(e);
            }
        }
        return r;
    }
}
